﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ThanhTu.GNC.Media.KQXS.DataAccess.DO;
using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace ThanhTu.GNC.Media.KQXS.DataAccess.DAL
{
    public class SenderMessageDAL : BaseDAL
    {
        public SenderMessageDAL() : base() { }
        public int SaveSenderMessage(SenderMessageDO vSenderMessageDO)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "SENDERMESSAGE_PKG.SAVESENDERMESSE";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PHONE", OracleDbType.Varchar2);
                oraParam.Value = vSenderMessageDO.Phone;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = vSenderMessageDO.Province_ID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_SYNTAX", OracleDbType.Varchar2);
                oraParam.Value = vSenderMessageDO.Syntax;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_SWITCHBOARD", OracleDbType.Varchar2);
                oraParam.Value = vSenderMessageDO.Switch_board;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_STATUS", OracleDbType.Int32);
                oraParam.Value = vSenderMessageDO.Status;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_DAYREGISTER", OracleDbType.Date);
                oraParam.Value = vSenderMessageDO.Dayregister;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_MOSEQ", OracleDbType.Varchar2);
                oraParam.Value = vSenderMessageDO.Moseq;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_MTSEQ", OracleDbType.Int32);
                oraParam.Value = vSenderMessageDO.Mtseq;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);


                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public SenderMessageDO GetBySenderMessage(string phone, string provinceid, string switchboard)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "SENDERMESSAGE_PKG.GETBYSENDERMESS";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PHONE", OracleDbType.Varchar2);
                oraParam.Value = phone;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = provinceid;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_SWITCHBOARD", OracleDbType.Varchar2);
                oraParam.Value = switchboard;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataRow dr = null;
                    SenderMessageDO vSenderMessageDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                dr = ds.Tables[0].Rows[0];
                                vSenderMessageDO = new SenderMessageDO();
                                if (!Convert.IsDBNull(dr["PHONE"]))
                                { vSenderMessageDO.Phone = Convert.ToString(dr["PHONE"]); }

                                if (!Convert.IsDBNull(dr["PROVINCEID"]))
                                { vSenderMessageDO.Province_ID = Convert.ToString(dr["PROVINCEID"]); }

                                if (!Convert.IsDBNull(dr["SYNTAX"]))
                                { vSenderMessageDO.Syntax = Convert.ToString(dr["SYNTAX"]); }

                                if (!Convert.IsDBNull(dr["SWITCHBOARD"]))
                                { vSenderMessageDO.Switch_board = Convert.ToString(dr["SWITCHBOARD"]); }

                                if (!Convert.IsDBNull(dr["STATUS"]))
                                { vSenderMessageDO.Status = Convert.ToInt32(dr["STATUS"]); }

                                if (!Convert.IsDBNull(dr["DAYREGISTER"]))
                                { vSenderMessageDO.Dayregister = Convert.ToDateTime(dr["DAYREGISTER"]); }

                                if (!Convert.IsDBNull(dr["MOSEQ"]))
                                { vSenderMessageDO.Moseq = Convert.ToString(dr["MOSEQ"]); }

                                if (!Convert.IsDBNull(dr["MTSEQ"]))
                                { vSenderMessageDO.Mtseq = Convert.ToInt32(dr["MTSEQ"]); }
                                dr = null;
                            }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return vSenderMessageDO;
                }
            }
        }
        public List<SenderMessageDO> SelectAll()
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "SENDERMESSAGE_PKG.GETALLSENDERMESS";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;
                    List<SenderMessageDO> vSenderMessageDOCollection = null;
                    DataRow dr = null;
                    SenderMessageDO vSenderMessageDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                            vSenderMessageDOCollection = new List<SenderMessageDO>();

                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                dr = dt.Rows[i];
                                vSenderMessageDO = new SenderMessageDO();

                                if (!Convert.IsDBNull(dr["PHONE"]))
                                { vSenderMessageDO.Phone = Convert.ToString(dr["PHONE"]); }

                                if (!Convert.IsDBNull(dr["PROVINCEID"]))
                                { vSenderMessageDO.Province_ID = Convert.ToString(dr["PROVINCEID"]); }

                                if (!Convert.IsDBNull(dr["SYNTAX"]))
                                { vSenderMessageDO.Syntax = Convert.ToString(dr["SYNTAX"]); }

                                if (!Convert.IsDBNull(dr["SWITCHBOARD"]))
                                { vSenderMessageDO.Switch_board = Convert.ToString(dr["SWITCHBOARD"]); }

                                if (!Convert.IsDBNull(dr["STATUS"]))
                                { vSenderMessageDO.Status = Convert.ToInt32(dr["STATUS"]); }

                                if (!Convert.IsDBNull(dr["DAYREGISTER"]))
                                { vSenderMessageDO.Dayregister = Convert.ToDateTime(dr["DAYREGISTER"]); }

                                if (!Convert.IsDBNull(dr["MOSEQ"]))
                                { vSenderMessageDO.Moseq = Convert.ToString(dr["MOSEQ"]); }

                                if (!Convert.IsDBNull(dr["MTSEQ"]))
                                { vSenderMessageDO.Mtseq = Convert.ToInt32(dr["MTSEQ"]); }

                                vSenderMessageDOCollection.Add(vSenderMessageDO);
                                dr = null;
                                vSenderMessageDO = null;
                            }

                            dt.Dispose();
                            dt = null;
                        }

                        ds.Dispose();
                    }
                    oraComm.Dispose();
                    oraParam = null;

                    return vSenderMessageDOCollection;
                }
            }
        }
    }
}
